package util

import (
	"database/sql"
	"math"
	"nspel/entity"
	"nspel/nspel"
	"strconv"
	"time"
)

type totalScore struct {
	TotalScore float64 `orm:"column(totalScore)"`
	TotalCount int64   `orm:"column(totalCount)"`
}

func UpdateSchoolScore(schoolId int64) error {
	var queryResult totalScore
	lastYearDate := time.Now().AddDate(-1, 0, 0).Format("2006-01-02")
	r := nspel.GetOrmEngine().Raw("SELECT SUM(physical_total_score) AS totalScore, COUNT(id) AS totalCount FROM score WHERE school_id = ? AND missing_test_num = 0 AND measurement_date >="+lastYearDate, schoolId)
	err := r.QueryRow(&queryResult)
	if err != nil {
		return err
	}
	score := queryResult.TotalScore / float64(queryResult.TotalCount)
	school := new(entity.School)
	school.Id = schoolId
	school.Score = sql.NullFloat64{Float64: score, Valid: true}
	err = school.Update("score")
	return err
}

func UpdateAreaScore(areaId int64) error {
	var queryResult totalScore
	lastYearDate := time.Now().AddDate(-1, 0, 0).Format("2006-01-02")
	r := nspel.GetOrmEngine().Raw("SELECT SUM(physical_total_score) AS totalScore, COUNT(id) AS totalCount FROM score WHERE experiment_area_id = ? AND missing_test_num = 0 AND measurement_date >="+lastYearDate, areaId)
	err := r.QueryRow(&queryResult)
	if err != nil {
		return err
	}
	score := queryResult.TotalScore / float64(queryResult.TotalCount)
	area := new(entity.Area)
	area.Id = areaId
	area.Score = sql.NullFloat64{Float64: score, Valid: true}
	err = area.Update("score")
	return err
}

func LevelClassification(level string, level1 string, level2 string, level3 string, level4 string, classification1 interface{}, classification2 interface{}, classification3 interface{}, classification4 interface{}, classification5 interface{}) interface{} {
	if level == level1 {
		return classification1
	} else if level == level2 {
		return classification2
	} else if level == level3 {
		return classification3
	} else if level == level4 {
		return classification4
	} else {
		return classification5
	}
}

func GetPhysicalList(provinceId int64, cityId int64, areaId int64, schoolName string, joinYear int64, schoolRollId string, pageSize int64, currentPage int64) (count int64, physical []entity.Score, err error) {
	score := new(entity.Score)
	ormEng := nspel.GetOrmEngine().QueryTable(score.TableName())
	hashKey := ""
	if provinceId != 0 || cityId != 0 || areaId != 0 || schoolName != "" || joinYear != 0 {
		_, schools, err := GetSchoolsListAdministrator(provinceId, cityId, areaId, schoolName, joinYear, math.MaxInt32, 1)
		if err != nil {
			return 0, nil, err
		}
		if len(schools) == 0 {
			return 0, []entity.Score{}, nil
		}
		schoolId := make([]int64, len(schools))
		for i := 0; i < len(schools); i++ {
			schoolId[i] = schools[i].Id
		}
		ormEng = ormEng.Filter("school_id__in", schoolId)
		if provinceId != 0 {
			hashKey += "provinceId[" + strconv.FormatInt(provinceId, 10) + "]."
		}
		if cityId != 0 {
			hashKey += "cityId[" + strconv.FormatInt(cityId, 10) + "]."
		}
		if areaId != 0 {
			hashKey += "areaId[" + strconv.FormatInt(areaId, 10) + "]."
		}
		hashKey += "schoolName[\"" + schoolName + "\"]."
		if joinYear != 0 {
			hashKey += "joinYear[" + strconv.FormatInt(joinYear, 10) + "]."
		}
	}
	ormEng = ormEng.Filter("school_roll_id__contains", schoolRollId)
	hashKey += "schoolRollId[" + schoolRollId + "]."
	count, err = score.CustomCount(&ormEng, hashKey+"count")
	hashKey += "pageSize[" + strconv.FormatInt(pageSize, 10) + "]."
	hashKey += "currentPage[" + strconv.FormatInt(currentPage, 10) + "]"
	offset := (currentPage - 1) * pageSize
	ormEng = ormEng.Limit(pageSize, offset)
	physical, err = score.CustomSelectAll(&ormEng, hashKey)
	return
}
